/*
This function is for generating FullAddressText with a specified FullAddress, Salutation and Formula.
It mimic behaviors of asi_ProcessFormula, but only used for FullAddress.
It is used when correspondence management needs to override user's preferred communication reason or
salutation. It is currently used from vFullAddressCorrespondenceReport.

Usage:
dbo.asi_GetFullAddressText(fullAddressKey,contactKey,physicalAddressKey,salutationKey,formula)

*/
CREATE  Function [dbo].[asi_GetFullAddressText]
        (@fullAddressKey as uniqueidentifier,
         @contactKey as uniqueidentifier,
         @addressKey as uniqueidentifier,
         @salutationKey as uniqueidentifier,
         @formula as nvarchar(500))
RETURNS nvarchar(1000)
BEGIN

  DECLARE
    @FieldName nvarchar(200),
    @FieldValue nvarchar(500),
    @output nvarchar(1000)

  SET @output = @formula

  DECLARE c_Components CURSOR FOR
  SELECT FieldName, FieldValue
    FROM dbo.asi_GetFullAddressTextComponents(@fullAddressKey,@contactKey,@addressKey,@salutationKey)

  OPEN c_Components

  FETCH NEXT FROM c_Components
   INTO @FieldName, @FieldValue
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF CHARINDEX(@FieldName, @output) > 0 AND @FieldValue IS NOT NULL BEGIN
      SET @output = REPLACE(@output,'[' + @FieldName + ']', @FieldValue)
      SET @output = REPLACE(@output,'[<u>' + @FieldName + ']', UPPER(@FieldValue))
    END

    FETCH NEXT FROM c_Components
     INTO @FieldName, @FieldValue
  END

  CLOSE c_Components
  DEALLOCATE c_Components

  DECLARE
   @startIndex int,
   @endIndex int,
   @bracketStartIndex int,
   @bracketEndIndex int
  SET @startIndex = CHARINDEX('{',@output)

  while @startIndex <= LEN(@output) begin
      SET @endIndex = CHARINDEX('}',@output,@startIndex)
      IF @endIndex = 0
        BREAK
      SET @bracketStartIndex = CHARINDEX('[',@output,@startIndex)

      IF @bracketStartIndex > 0 BEGIN
          SET @bracketEndIndex = CHARINDEX(']',@output,@bracketStartIndex)
          IF @bracketEndIndex > 0 BEGIN
            SET @output = Substring(@output,1,@startIndex-1) + Substring(@output,@endIndex + 1,LEN(@output) - @endIndex)
          END
      END
      ELSE BEGIN
        SET @output = REPLACE(REPLACE(@output,'{',''),'}','')
      END

      SET @startIndex = CHARINDEX('{',@output)
  END
  
  SET @output = REPLACE(@output,'<n>',char(13) + char(10))
  SET @output = REPLACE(@output,'<t>',char(9))

  RETURN @output
END

GO